#!/usr/bin/env python
# coding=utf-8
# __author__ = 'Yunchao Ling'

import re
import optparse
import MySQLdb

parser = optparse.OptionParser()
parser.add_option("-H", "--host", dest="host", help="MySQL host name/IP", default="localhost")
parser.add_option("-u", "--username", dest="user", help="MySQL host username")
parser.add_option("-p", "--password", dest="password", help="MySQL host password")
parser.add_option("-P", "--port", dest="port", help="MySQL host port", default="3306")
parser.add_option("-d", "--database", dest="db", help="MySQL host database")
parser.add_option("-t", "--table", dest="table", help="MySQL host table")
parser.add_option("-T", "--title", action="store_true", dest="title", help="Input file has a title line ",
                  default=False)
parser.add_option("-i", "--input", dest="input", help="input file")
parser.add_option("-o", "--order", dest="order", help="column order")
parser.add_option("-s", "--split", dest="splitsymbol", help="split symbol", default="\t")

(options, args) = parser.parse_args()

conn = MySQLdb.connect(host=options.host, user=options.user, passwd=options.password, db=options.db, charset="utf8")
count=0
inputfile = open(options.input, "r")
if options.title:
    inputfile.readline()
for line in inputfile:
    line=line.decode('utf-8','ignore').encode('utf-8')
    line = line.rstrip("\r\n")
    splitline = line.split(options.splitsymbol)
    length = len(splitline)
    val = ""
    cursor = conn.cursor()
    params=()
    if options.order==None:
        arr=[]
        for item in splitline:
            if item=="":
                val+="null,"
            else:
                val += "%s,"
                if item=="True":
                    item=1
                elif item=="False":
                    item=0
                elif re.match(r'^\d*$',item):
                    item=int(item)
                arr.append(item)
        val = val[:-1]
        params=tuple(arr)
    else:
        arr=[]
        sp=options.order.split(",")
        for item in sp:
            item2=splitline[int(item)]
            if item2=="":
                val+="null,"
            else:
                val += "%s,"
                if item2=="True":
                    item2=1
                elif item2=="False":
                    item2=0
                elif re.match(r'^\d*$',item2):
                    item2=int(item2)
                arr.append(item2)
        val = val[:-1]
        params=tuple(arr)
    sql = "insert into " + options.table + " values(" + val + ")"
    result = cursor.execute(sql, params)
    conn.commit()
    cursor.close()
    count+=1
    print str(count)+" records has been inserted."

conn.close()
inputfile.close()